package com.senpai.database;

import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "Mydatabase";

	private static final String TABLE_ANSWER = "answer";
	private static final String KEY_ID = "id";
	private static final String KEY_PARTI = "parti";
	private static final String KEY_ANSWER = "id_answer";

	private static final int COUNT = 0;

	private static final String TABLE_QUESTION = "reponse";
	private static final String KEY_ID_Q = "id";
	private static final String KEY_NUM = "num";
	private static final String KEY_CATEGORY = "question";
	private static final String KEY_QUESTION = "category";
	private static final String KEY_DATE = "date";

	private static final String CREATE_TABLE_ANSWER = "CREATE TABLE answer ( "
			+ "id INTEGER PRIMARY KEY, " + "parti TEXT," + "id_answer TEXT)";

	private static final String CREATE_TABLE_QUESTION = "CREATE TABLE reponse ( "
			+ "id INTEGER PRIMARY KEY, "
			+ "num TEXT,"
			+ "question TEXT, "
			+ "category TEXT, " + "date TEXT)";

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_TABLE_ANSWER);
		db.execSQL(CREATE_TABLE_QUESTION);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANSWER);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUESTION);

	}

	private static final String[] COLUMNS_QUESTION = { KEY_ID_Q, KEY_NUM,
			KEY_QUESTION, KEY_CATEGORY, KEY_DATE };

	private static final String[] COLUMNS_ANSWER = { KEY_ID, KEY_PARTI,
			KEY_ANSWER };

	public void addQuestion(Question question) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(KEY_ID_Q, question.getId());
		values.put(KEY_NUM, question.getNum());
		values.put(KEY_QUESTION, question.getQuestion());
		values.put(KEY_CATEGORY, question.getCategory());
		values.put(KEY_DATE, question.getDate());

		db.insert(TABLE_QUESTION, null, values);
		db.close();
	}

	public Question getQuestion(int id) {

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_QUESTION, COLUMNS_QUESTION, " id = ?",
				new String[] { String.valueOf(id) }, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		Question question = new Question();
		question.setId(Integer.parseInt(cursor.getString(0)));
		question.setNum(cursor.getString(1));
		question.setCategory(cursor.getString(2));
		question.setQuestion(cursor.getString(3));
		question.setDate(cursor.getString(4));

		return question;
	}

	public Question getQuestionById(String id) {

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_QUESTION, COLUMNS_QUESTION, " num = ?",
				new String[] { String.valueOf(id) }, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		Question question = new Question();
		question.setId(Integer.parseInt(cursor.getString(0)));
		question.setNum(cursor.getString(1));
		question.setCategory(cursor.getString(2));
		question.setQuestion(cursor.getString(3));
		question.setDate(cursor.getString(4));

		return question;
	}

	public List<Question> getAllQuestions() {
		List<Question> questionList = new LinkedList<Question>();

		String query = "SELECT  * FROM " + TABLE_QUESTION;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Question question = null;
		if (cursor.moveToFirst()) {
			do {
				question = new Question();
				question.setId(Integer.parseInt(cursor.getString(0)));
				question.setNum(cursor.getString(1));
				question.setCategory(cursor.getString(2));
				question.setQuestion(cursor.getString(3));
				question.setDate(cursor.getString(4));
				questionList.add(question);
			} while (cursor.moveToNext());
		}

		return questionList;
	}

	public void addAnswer(Answer answer) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ID, answer.getId());
		values.put(KEY_PARTI, answer.getParti());
		values.put(KEY_ANSWER, answer.getId_answer());
		db.insert(TABLE_ANSWER, null, values);
		db.close();
	}

	public Answer getAnswer(int id) {

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_ANSWER, COLUMNS_ANSWER, " id = ?",
				new String[] { String.valueOf(id) }, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		Answer answer = new Answer();
		answer.setId(Integer.parseInt(cursor.getString(0)));
		answer.setParti(cursor.getString(1));
		answer.setId_answer(cursor.getString(2));

		return answer;
	}

	public List<Answer> getAllAnswers() {
		List<Answer> answerList = new LinkedList<Answer>();

		String query = "SELECT  * FROM " + TABLE_ANSWER;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Answer answer = null;
		if (cursor.moveToFirst()) {
			do {
				answer = new Answer();
				answer.setId(Integer.parseInt(cursor.getString(0)));
				answer.setParti(cursor.getString(1));
				answer.setId_answer(cursor.getString(2));
				answerList.add(answer);
			} while (cursor.moveToNext());
		}

		return answerList;
	}

	public int match() {

		String query = "SELECT count(answer.id_answer) FROM answer, reponse WHERE answer.id_answer = reponse.num ";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		int x = 0;
		if (cursor.moveToFirst()) {
			x = cursor.getInt(0);
		}
		cursor.close();
		return x;

	}

}
